﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;

using dbUtils.dto;

namespace dbUtils
{
    public enum SQL_RETURN {ERROR, SUSSCESS};

    public class dbTable : db
    {
        protected DataTable _datatable;
        protected string _command;
        protected int _command_type;
        protected static int COMMAND_TYPE_INSERT = 0;
        protected static int COMMAND_TYPE_UPDATE = COMMAND_TYPE_INSERT + 1;
        protected static int COMMAND_TYPE_DELETE = COMMAND_TYPE_INSERT + 2;
        protected static int COMMAND_TYPE_SELECT = COMMAND_TYPE_INSERT + 3;

#region CONS
        public dbTable() {}

        public dbTable(string query)
        {
            _command = query;
            _command_type = dbTable.COMMAND_TYPE_SELECT;
            _datatable = null;
        }
#endregion

#region PROP
        public string gString(string s)
        {
            return "'" + s + "'";
        }

        public string gInt(int i)
        {
            return i.ToString();
        }

        public string Command
        {
            set { _command = value; }
        }

        public DataTable Select()
        {
            _command_type = COMMAND_TYPE_SELECT;
            Execute();
            return _datatable;
        }

        public void Insert()
        {
            _command_type = COMMAND_TYPE_INSERT;
            Execute();
        }

        public void Update()
        {
            _command_type = COMMAND_TYPE_UPDATE;
            Execute();
        }

        public void Delete()
        {
            _command_type = COMMAND_TYPE_DELETE;
            Execute();
        }

        protected SQL_RETURN Execute()
        {
            SQL_RETURN code = SQL_RETURN.ERROR;
            SqlConnection sqlcon = null;
            try
            {
                sqlcon = new SqlConnection(_constr);
                sqlcon.Open();
                SqlCommand cmd = sqlcon.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = _command;
                if (_command_type == COMMAND_TYPE_SELECT)
                {
                    SqlDataAdapter adap = new SqlDataAdapter();
                    if (_datatable != null)
                        _datatable.Dispose();
                    _datatable = new DataTable();
                    adap.SelectCommand = cmd;
                    adap.Fill(_datatable);
                    code = SQL_RETURN.SUSSCESS;
                }
                else if (_command_type == COMMAND_TYPE_INSERT ||
                    _command_type == COMMAND_TYPE_UPDATE || _command_type == COMMAND_TYPE_DELETE)
                {
                    cmd.ExecuteNonQuery();
                    code = SQL_RETURN.SUSSCESS;
                }
            }
            catch (System.Exception ex)
            {
                code = SQL_RETURN.ERROR;
            }
            finally
            {
                if (sqlcon != null) sqlcon.Close();
            }
            return code;
        }
#endregion

#region _INSERT_OBJDTO
        public SQL_RETURN Insert(objdto obj)
        {
            _command_type = COMMAND_TYPE_INSERT;
            switch (obj.ObjType)
            {
                case objdto.OBJ_TYPE_DIADIEM:
                    DiaDiem dd = (DiaDiem)obj;
                    _command = "insert into [diadiem](ten,sdt,diachi";
                    if (dd.Type == DiaDiem.TYPE_SPEC)
                        _command += ",type";
                    _command += ")values(";
                    _command += gString(dd.Ten) + "," + gString(dd.Sdt) + "," + gString(dd.Diachi);
                    if (dd.Type == DiaDiem.TYPE_SPEC)
                        _command += "," + gInt(dd.Type);
                    _command += ")";
                    break;



                case objdto.OBJ_TYPE_USER:
                    User us = (User)obj;
                    _command = "insert into [user](username,password,sdt,cmnd,email,hoten";
                    if (us.Type != User.TYPE_DEFL_MEMBER)
                        _command += ",type";
                    _command += ")values(";
                    _command += gString(us.UserName) + "," + gString(us.Password) + "," + gString(us.Sdt) + "," + gString(us.Cmnd) + "," + gString(us.Email) + "," + gString(us.Hoten);
                    if (us.Type != User.TYPE_DEFL_MEMBER)
                        _command += "," + gInt(us.Type);
                    _command += ")";
                    break;

                case objdto.OBJ_TYPE_TUYEN:
                    Tuyen t = (Tuyen)obj;
                    _command = "insert into [tuyen](ten, id_diadiem1, id_diadiem2) values(";
                    _command += gString(t.Ten) + "," + gInt(t.Id_diadiem1) + "," + gInt(t.Id_diadiem2) + ")";
                    break;



                default:
                    return SQL_RETURN.ERROR;
            }
            return Execute();
        }
#endregion

#region GET_INFO_USER
        public int getUserType(string username)
        {
            _command = "select id from [user] where  username like " + gString(username);
            _command_type = COMMAND_TYPE_SELECT;
            if (Execute() == SQL_RETURN.SUSSCESS)
                return int.Parse(_datatable.Rows[0][0].ToString());
            return User.ID_ERROR;
        }

        public int getUserType(int userid)
        {
            _command = "select id from [user] where id = " + gInt(userid);
            _command_type = COMMAND_TYPE_SELECT;
            if (Execute() == SQL_RETURN.SUSSCESS)
                return int.Parse(_datatable.Rows[0][0].ToString());
            return User.ID_ERROR;
        }
#endregion
    }
}
